package com.dd.service;


import com.dd.model.ColumnInfo;
import javafx.scene.control.Alert;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class JdbcSchemaCompareUtil {
    public static void main(String[] args) {
        String createTableSQL = JdbcSchemaCompareUtil.getCreateTableSQL("crmx_plan", "order_pn_supply_mr_report");
        System.out.println(createTableSQL);
    }

    public static List<String> getDbTables(Connection conn) {
        List<String> tables = new ArrayList<>();
        try {
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SHOW TABLES")) {
                while (rs.next()) {
                    tables.add(rs.getString(1));
                }
            }
        } catch (SQLException e) {
            System.out.println("查询生产环境表列表失败: " + e.getMessage());
        }
        return tables;
    }


    public static List<String> compareTableStructure(String dsName, String tableName) {
        List<String> list = new ArrayList<>();
        try {
            Connection sourceConn = JdbcConnectionFactory.getSourceConnection(dsName);
            Connection targetConn = JdbcConnectionFactory.getTargetConnection(dsName);
            // 检查源数据库中是否存在该表
            List<String> testTables = getDbTables(sourceConn);

            // 检查目标数据库中是否存在该表
            if (!testTables.contains(tableName)) {
                showAlert("表" + tableName + " 在目标数据库中不存在");
                return list;
            }

            // 比较每个表的结构并生成ALTER语句
            list = compareTableStructure(tableName, targetConn, sourceConn);
        } catch (SQLException e) {
            System.out.println("执行过程中发生错误: " + e.getMessage());
        }
        return list;
    }

    public static String getCreateTableSQL(String dsName, String tableName) {
        StringBuilder createTableSQL = new StringBuilder();
        try {
            Connection conn = JdbcConnectionFactory.getSourceConnection(dsName);
            String sql = "SHOW CREATE TABLE " + tableName;
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
                if (rs.next()) {
                    createTableSQL.append(rs.getString(2));
                } else {
                    System.out.println("错误：表 '" + tableName + "' 不存在");
                }
            }
        } catch (SQLException e) {
            System.out.println("获取 CREATE TABLE 语句失败: " + e.getMessage());
            e.printStackTrace();
        }
        return createTableSQL.toString();
    }

    public static boolean checkTableExist(Connection sourceConn, String tableName) {
        // 检查源数据库中是否存在该表
        List<String> testTables = getDbTables(sourceConn);

        // 检查目标数据库中是否存在该表
        if (!testTables.contains(tableName)) {
            return false;
        } else {
            return true;
        }
    }

    private static List<String> compareTableStructure(String tableName, Connection conn1, Connection conn2) throws SQLException {
        // 获取两个数据库中表的列信息
        Map<String, ColumnInfo> columns1 = getColumnInfo(tableName, conn1);
        Map<String, ColumnInfo> columns2 = getColumnInfo(tableName, conn2);

        // 比较列差异并生成ALTER语句
        List<String> alterStatements = generateAlterStatements(tableName, columns1, columns2);

        // 输出ALTER语句
        if (!alterStatements.isEmpty()) {
            System.out.println("\n--- 表 " + tableName + " 的结构差异 ---");
            alterStatements.forEach(System.out::println);
        } else {
            // System.out.println("\n表 " + tableName + " 结构完全一致");
        }
        return alterStatements;
    }

    private static Map<String, ColumnInfo> getColumnInfo(String tableName, Connection conn) throws SQLException {
        Map<String, ColumnInfo> columns = new LinkedHashMap<>();
        String catalog = conn.getCatalog();

        String sql = "SELECT COLUMN_NAME, COLUMN_TYPE, DATA_TYPE, " + "CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, " + "NUMERIC_SCALE, IS_NULLABLE, COLUMN_DEFAULT, " + "EXTRA, COLUMN_COMMENT " + "FROM INFORMATION_SCHEMA.COLUMNS " + "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, catalog);
            pstmt.setString(2, tableName);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String columnName = rs.getString("COLUMN_NAME");
                    String columnType = rs.getString("COLUMN_TYPE");
                    String dataType = rs.getString("DATA_TYPE");
                    long size = getLong(rs, "CHARACTER_MAXIMUM_LENGTH");
                    if (size == 0) {
                        size = getLong(rs, "NUMERIC_PRECISION");
                    }
                    long decimalDigits = getLong(rs, "NUMERIC_SCALE");
                    boolean nullable = "YES".equalsIgnoreCase(rs.getString("IS_NULLABLE"));
                    String defaultValue = rs.getString("COLUMN_DEFAULT");
                    String extra = rs.getString("EXTRA");
                    boolean autoIncrement = extra != null && extra.contains("auto_increment");
                    String comment = rs.getString("COLUMN_COMMENT");

                    ColumnInfo column = new ColumnInfo(columnName, dataType, columnType, size, decimalDigits, nullable, defaultValue, autoIncrement, comment);
                    columns.put(column.getName(), column);
                }
            }
        }
        return columns;
    }

    private static long getLong(ResultSet rs, String columnName) throws SQLException {
        long value = rs.getLong(columnName);
        return rs.wasNull() ? 0L : value;
    }

    private static List<String> generateAlterStatements(String tableName, Map<String, ColumnInfo> source, Map<String, ColumnInfo> target) {
        List<String> statements = new ArrayList<>();

        // 添加缺失的列
        for (Map.Entry<String, ColumnInfo> entry : target.entrySet()) {
            String columnName = entry.getKey();
            if (!source.containsKey(columnName)) {
                ColumnInfo column = entry.getValue();
                statements.add(String.format("ALTER TABLE %s ADD COLUMN %s %s COMMENT '%s';", tableName, column.getName(), column.getDefinition(), escapeSqlString(column.getComment())));
            }
        }

        // 删除多余的列
        for (Map.Entry<String, ColumnInfo> entry : source.entrySet()) {
            String columnName = entry.getKey();
            if (!target.containsKey(columnName)) {
                statements.add(String.format("ALTER TABLE %s DROP COLUMN %s;", tableName, columnName));
            }
        }

        // 修改已存在列的定义
        for (Map.Entry<String, ColumnInfo> entry : source.entrySet()) {
            String columnName = entry.getKey();
            ColumnInfo sourceColumn = entry.getValue();
            ColumnInfo targetColumn = target.get(columnName);

            if (targetColumn != null && !sourceColumn.equals(targetColumn)) {
                StringBuilder alterClause = new StringBuilder();
                alterClause.append("ALTER TABLE ")
                        .append(tableName)
                        .append(" MODIFY COLUMN ")
                        .append(columnName)
                        .append(" ");

                // 使用目标列的完整定义
                alterClause.append(targetColumn.getDefinition());

                // 添加注释
                if (targetColumn.getComment() != null && !targetColumn.getComment()
                        .isEmpty()) {
                    alterClause.append(" COMMENT '")
                            .append(escapeSqlString(targetColumn.getComment()))
                            .append("'");
                }

                // 保持列顺序与目标数据库一致
                List<String> targetColumnNames = new ArrayList<>(target.keySet());
                int targetPosition = targetColumnNames.indexOf(columnName);

                if (targetPosition > 0) {
                    String previousColumnName = targetColumnNames.get(targetPosition - 1);
                    alterClause.append(" AFTER ")
                            .append(previousColumnName);
                } else {
                    alterClause.append(" FIRST");
                }

                alterClause.append(";");
                statements.add(alterClause.toString());
            }
        }

        return statements;
    }

    private static void showAlert(String message) {
        Alert alert = new Alert(Alert.AlertType.INFORMATION);
        alert.setTitle("提示");
        alert.setHeaderText(null);
        alert.setContentText(message);
        alert.showAndWait();
    }

    private static String escapeSqlString(String value) {
        if (value == null) {
            return "";
        }
        return value.replace("'", "''");
    }
}